﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataAccess {
    public class ProductRespository : DataProvider {
        /// <summary>
        /// get all products
        /// </summary>
        /// <returns></returns>
        public DataTable getProducts() {
            String query = "SELECT ProductID, ProductName, ProductPrice, ProductPhotoURL, ProductDescription, UpdateDate, QuantityAtLeast, OtherInfo FROM Products ORDER BY UpdateDate DESC";
            return this.executeQueryDataTable(query);
        }

        public DataTable getProducts(int catID) {
            String query = "SELECT ProductID, ProductName, ProductPrice, ProductPhotoURL, ProductDescription, UpdateDate, QuantityAtLeast, OtherInfo FROM Products WHERE CatID = " + catID + " ORDER BY UpdateDate DESC";
            return this.executeQueryDataTable(query);
        }

        /// <summary>
        /// get a product
        /// </summary>
        /// <param name="productID"></param>
        /// <returns></returns>
        public DataTable getProduct(int productID) {
            //String query = "SELECT ProductID, ProductName, ProductPrice, ProductPhotoURL, ProductDescription, UpdateDate, QuantityAtLeast, OtherInfo FROM Products WHERE ProductID = " + productID;
            String query = "SELECT * FROM Products WHERE ProductID = " + productID;
            return this.executeQueryDataTable(query);
        }

        /// <summary>
        /// insert new product
        /// </summary>
        /// <param name="name"></param>
        /// <param name="catID"></param>
        /// <param name="price"></param>
        /// <param name="photoUrl"></param>
        /// <param name="description"></param>
        /// <param name="otherInfo"></param>
        /// <param name="Instock"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        /// <param name="length"></param>
        /// <param name="weight"></param>
        /// <returns></returns>
        public int add(String name, int catID, float price, String photoUrl, String description, String otherInfo, int Instock, float width, float height, float length, float weight, int quantityAtLeast) {
            String sql = "INSERT INTO PRODUCTS(ProductName, CatID, ProductPrice, ProductPhotoURL, ProductDescription, OtherInfo, InStock, Width, Height, Length, Weight, QuantityAtLeast, UpdateDate) VALUES(@ProductName, @CatID, @ProductPrice, @ProductPhotoURL, @ProductDescription, @OtherInfo, @InStock, @Width, @Height, @Length, @Weight, @QuantityAtLeast, @UpdateDate)";
            _sqlCmd = new System.Data.SqlClient.SqlCommand();
            _sqlCmd.Connection = this._sqlConn;
            _sqlCmd.CommandText = sql;
            _sqlCmd.CommandType = CommandType.Text;

            _sqlCmd.Parameters.Add("@ProductName", SqlDbType.NVarChar);
            _sqlCmd.Parameters.Add("@CatID", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@ProductPrice", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@ProductPhotoURL", SqlDbType.NChar);
            _sqlCmd.Parameters.Add("@ProductDescription", SqlDbType.NText);
            _sqlCmd.Parameters.Add("@OtherInfo", SqlDbType.NText);
            _sqlCmd.Parameters.Add("@InStock", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@Width", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Height", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Length", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Weight", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@QuantityAtLeast", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@UpdateDate", SqlDbType.DateTime);

            _sqlCmd.Parameters["@ProductName"].Value = name;
            _sqlCmd.Parameters["@CatID"].Value = catID;
            _sqlCmd.Parameters["@ProductPrice"].Value = price;
            _sqlCmd.Parameters["@ProductPhotoURL"].Value = photoUrl;
            _sqlCmd.Parameters["@ProductDescription"].Value = description;
            _sqlCmd.Parameters["@OtherInfo"].Value = otherInfo;
            _sqlCmd.Parameters["@InStock"].Value = Instock;
            _sqlCmd.Parameters["@Width"].Value = width;
            _sqlCmd.Parameters["@Height"].Value = height;
            _sqlCmd.Parameters["@Length"].Value = length;
            _sqlCmd.Parameters["@Weight"].Value = weight;
            _sqlCmd.Parameters["@QuantityAtLeast"].Value = quantityAtLeast;
            _sqlCmd.Parameters["@UpdateDate"].Value = DateTime.Now;

            connect();
            _sqlCmd.ExecuteNonQuery();
            disConnect();
            
            sql = "SELECT MAX(ProductID) FROM Products";
            return int.Parse(this.executeScalar(sql).ToString());                            
        }

        public bool update(int id, String name, int catID, float price, String photoUrl, String description, String otherInfo, int Instock, float width, float height, float length, float weight, int quantityAtLeast) {
            String sql = null;
            if (photoUrl.Equals("")) {
                sql = "UPDATE Products SET ProductName = @ProductName, CatID = @CatID, ProductPrice = @ProductPrice, QuantityAtLeast = @QuantityAtLeast, ProductDescription = @ProductDescription, OtherInfo = @OtherInfo, InStock = @InStock, Width = @Width, Height = @Height, Length = @Length, Weight = @Weight, UpdateDate = @UpdateDate WHERE ProductID = @ProductID";

            }
            else {
                sql = "UPDATE Products SET ProductName = @ProductName, CatID = @CatID, ProductPrice = @ProductPrice, QuantityAtLeast = @QuantityAtLeast, ProductDescription = @ProductDescription, OtherInfo = @OtherInfo, InStock = @InStock, Width = @Width, Height = @Height, Length = @Length, Weight = @Weight, ProductPhotoURL = @ProductPhotoURL, UpdateDate = @UpdateDate WHERE ProductID = @ProductID";                
            }

            _sqlCmd = new System.Data.SqlClient.SqlCommand();
            _sqlCmd.Connection = this._sqlConn;
            _sqlCmd.CommandText = sql;
            _sqlCmd.CommandType = CommandType.Text;

            _sqlCmd.Parameters.Add("@ProductID", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@ProductName", SqlDbType.NVarChar);
            _sqlCmd.Parameters.Add("@CatID", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@ProductPrice", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@ProductPhotoURL", SqlDbType.NChar);
            _sqlCmd.Parameters.Add("@ProductDescription", SqlDbType.NText);
            _sqlCmd.Parameters.Add("@OtherInfo", SqlDbType.NText);
            _sqlCmd.Parameters.Add("@InStock", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@Width", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Height", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Length", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@Weight", SqlDbType.Float);
            _sqlCmd.Parameters.Add("@QuantityAtLeast", SqlDbType.Int);
            _sqlCmd.Parameters.Add("@UpdateDate", SqlDbType.DateTime);

            _sqlCmd.Parameters["@ProductID"].Value = id;
            _sqlCmd.Parameters["@ProductName"].Value = name;
            _sqlCmd.Parameters["@CatID"].Value = catID;
            _sqlCmd.Parameters["@ProductPrice"].Value = price;
            _sqlCmd.Parameters["@ProductPhotoURL"].Value = photoUrl;
            _sqlCmd.Parameters["@ProductDescription"].Value = description;
            _sqlCmd.Parameters["@OtherInfo"].Value = otherInfo;
            _sqlCmd.Parameters["@InStock"].Value = Instock;
            _sqlCmd.Parameters["@Width"].Value = width;
            _sqlCmd.Parameters["@Height"].Value = height;
            _sqlCmd.Parameters["@Length"].Value = length;
            _sqlCmd.Parameters["@Weight"].Value = weight;
            _sqlCmd.Parameters["@QuantityAtLeast"].Value = quantityAtLeast;
            _sqlCmd.Parameters["@UpdateDate"].Value = DateTime.Now;

            connect();
            int res = _sqlCmd.ExecuteNonQuery();
            disConnect();

            if (res > 0) return true;
            else return false;
        }

        public bool delete(int id) {
            String sql = "DELETE FROM Products WHERE ProductID = " + id;
            if (this.executeQuery(sql) > 0) {
                return true;
            }
            return false;
        }

        /*************web service******************/

        public DataTable wsGetProducts(int catID) {
            DataTable table = new DataTable();
            String sql = String.Format("SELECT * FROM Products WHERE CatID = {0}", catID);
            table = this.executeQueryDataTable(sql);
            return table;
        }
    }
}
